﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace iHub
{
    class AccountManager : dbConnector
    {
        private SqlDataReader read;
        private SqlCommand com;
        private SqlParameter param;
        private String username = "";
        private String password = "";
        private Boolean usertype = false;
        private Boolean status = true;
        private String question = "";
        private String answer = "";

        public int verifyLogin(Account a)
        {
            String username = a.getUsername();
            String password = a.getPass();
            int cased = 2;

            try
            {
                connect();

                com = new SqlCommand("select * from Accounts where username = @username and password = @password", getCon());
                param = new SqlParameter("@username", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = username;
                com.Parameters.Add(param);
                param = new SqlParameter("@password", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = password;
                com.Parameters.Add(param);
                read = com.ExecuteReader();

                if (read.HasRows == true)
                {
                    cased = 0;
                }

                read.Close();

                if (cased != 0)
                {
                    com = new SqlCommand("select * from Accounts where username = @username", getCon());
                    com.Parameters.AddWithValue("@username", username);
                    read = com.ExecuteReader();

                    if (read.HasRows == true)
                    {
                        cased = 1;
                    }

                    read.Close();
                }
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return cased;
        }

        public void setAccount(Account a)
        {
            String user = a.getUsername();
            String pass = a.getPass();

            try
            {
                connect();

                com = new SqlCommand("select * from Accounts where username = @user and password = @pass", getCon());
                param = new SqlParameter("@user", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = user;
                com.Parameters.Add(param);
                param = new SqlParameter("@pass", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = pass;
                com.Parameters.Add(param);
                read = com.ExecuteReader();

                while (read.Read())
                {
                    a.setUsertype(Convert.ToBoolean(read["usertype"]));
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public Boolean isTerminated(String username)
        {
            String user = username;
            Boolean allowed = false;

            try
            {
                connect();

                com = new SqlCommand("select status from Accounts where username = @user", getCon());
                param = new SqlParameter("@user", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = user;
                com.Parameters.Add(param);
                read = com.ExecuteReader();

                while (read.Read())
                {
                    allowed = Convert.ToBoolean(read["status"]);
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return allowed;
        }

        public String getQuestion(String username)
        {
            String question = "";

            try
            {
                connect();

                com = new SqlCommand("select securityquestion from Accounts where username = @username and status = 1", getCon());
                param = new SqlParameter("@username", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = username;
                com.Parameters.Add(param);
                read = com.ExecuteReader();

                while (read.Read())
                {
                    question = Convert.ToString(read["securityquestion"]);
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
            return question;
        }

        public Boolean verifyAnswer(String question, String username, String answer)
        {
            Boolean verified = false;

            try
            {
                connect();

                com = new SqlCommand("select count(*) as verify from Accounts where username = @username and securityquestion = @question and securityanswer = @answer", getCon());
                param = new SqlParameter("@username", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = username;
                com.Parameters.Add(param);
                param = new SqlParameter("@question", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = question;
                com.Parameters.Add(param);
                param = new SqlParameter("@answer", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = answer;
                com.Parameters.Add(param);
                read = com.ExecuteReader();

                while (read.Read())
                {
                    verified = Convert.ToBoolean(read["verify"]);
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return verified;
        }

        public DataTable viewLoginHistory()
        {
            DataTable history = new DataTable();
            String type = "";
            DateTime date;
            String d = "";
            String t = "";

            try
            {
                connect();

                com = new SqlCommand("select * from Login_History order by date desc, time desc", getCon());
                read = com.ExecuteReader();

                history.Columns.Add(new DataColumn("Username", typeof(string)));
                history.Columns.Add(new DataColumn("User Type", typeof(string)));
                history.Columns.Add(new DataColumn("Time of Access", typeof(string)));
                history.Columns.Add(new DataColumn("Date of Access", typeof(string)));

                while (read.Read())
                {
                    username = read["username"].ToString();
                    usertype = Convert.ToBoolean(read["usertype"]);

                    if (usertype)
                    {
                        type = "Administrator";
                    }
                    else
                    {
                        type = "Encoder";
                    }

                    date = Convert.ToDateTime(read["date"]);
                    d = date.ToShortDateString();

                    t = read["time"].ToString();

                    history.Rows.Add(username, type, t, d);
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return history;
        }

        public void updateLoginHistory(String username, Boolean usertype, DateTime date)
        {
            try
            {
                connect();

                com = new SqlCommand("INSERT INTO Login_History (username, date, time, usertype) VALUES (@user, @date, @time, @type)", getCon());

                param = new SqlParameter("@user", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = username;
                com.Parameters.Add(param);
                param = new SqlParameter("@date", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = date.ToShortDateString();
                com.Parameters.Add(param);
                param = new SqlParameter("@time", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = date.ToShortTimeString();
                com.Parameters.Add(param);
                param = new SqlParameter("@type", SqlDbType.Bit);
                param.Direction = ParameterDirection.Input;
                param.Value = usertype;
                com.Parameters.Add(param);

                com.ExecuteNonQuery();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public DataTable viewUserAccounts()
        {
            DataTable accounts = new DataTable();
            String type = "";
            String stat = "";

            try
            {
                connect();

                com = new SqlCommand("select * from Accounts", getCon());
                read = com.ExecuteReader();

                accounts.Columns.Add(new DataColumn("Username", typeof(string)));
                accounts.Columns.Add(new DataColumn("Password", typeof(string)));
                accounts.Columns.Add(new DataColumn("User Type", typeof(string)));
                accounts.Columns.Add(new DataColumn("Status", typeof(string)));
                accounts.Columns.Add(new DataColumn("Security Question", typeof(string)));
                accounts.Columns.Add(new DataColumn("Security Answer", typeof(string)));

                while (read.Read())
                {
                    username = read["username"].ToString();
                    password = read["password"].ToString();
                    usertype = Convert.ToBoolean(read["usertype"]);
                    status = Convert.ToBoolean(read["status"]);
                    question = read["securityquestion"].ToString();
                    answer = read["securityanswer"].ToString();

                    if (usertype)
                    {
                        type = "Administrator";
                    }
                    else
                    {
                        type = "Encoder";
                    }

                    if (status)
                    {
                        stat = "Active";
                    }
                    else
                    {
                        stat = "Inactive";
                    }

                    accounts.Rows.Add(username, password, type, stat, question, answer);
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return accounts;
        }

        public void addUserAccount(Account a)
        {
            username = a.getUsername();
            password = a.getPass();
            usertype = a.getUsertype();
            status = true;
            question = a.getQuestion();
            answer = a.getAnswer();
            int attempts = 0;

            try
            {
                connect();

                com = new SqlCommand("dbo.createAccount", getCon());
                com.CommandType = CommandType.StoredProcedure;

                param = new SqlParameter("@user", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = username;
                com.Parameters.Add(param);
                param = new SqlParameter("@pass", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = password;
                com.Parameters.Add(param);
                param = new SqlParameter("@type", SqlDbType.Bit);
                param.Direction = ParameterDirection.Input;
                param.Value = usertype;
                com.Parameters.Add(param);
                param = new SqlParameter("@status", SqlDbType.Bit);
                param.Direction = ParameterDirection.Input;
                param.Value = status;
                com.Parameters.Add(param);
                param = new SqlParameter("@question", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = question;
                com.Parameters.Add(param);
                param = new SqlParameter("@answer", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = answer;
                com.Parameters.Add(param);
                param = new SqlParameter("@attempts", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = attempts;
                com.Parameters.Add(param);

                com.ExecuteNonQuery();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public bool findAccount()
        {
            int count=0;
            bool check=false;

            try
            {
                connect();

                com = new SqlCommand("select count(*) as counttype from Accounts where usertype = 1", getCon());
                read = com.ExecuteReader();

                if (read.Read())
                {
                    count = Convert.ToInt32(read["counttype"]);

                    if (count >= 2)
                    {
                        check = true;
                    }
                }

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return check;
        }

        public void terminateAccount(String username)
        {
            try
            {
                connect();

                com = new SqlCommand("update Accounts set status = 0 where username = @username", getCon());
                param = new SqlParameter("@username", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = username;
                com.Parameters.Add(param);
                com.ExecuteNonQuery();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public void editPassword(String username, String password)
        {
            try
            {
                connect();

                com = new SqlCommand("update Accounts set password = @password where username = @username", getCon());
                param = new SqlParameter("@password", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = password;
                com.Parameters.Add(param);
                param = new SqlParameter("@username", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = username;
                com.Parameters.Add(param);
                com.ExecuteNonQuery();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public void editSecurity(String username, String question, String answer)
        {
            try
            {
                connect();

                com = new SqlCommand("update Accounts set securityquestion = @question, securityanswer = @answer where username = @username", getCon());
                param = new SqlParameter("@question", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = question;
                com.Parameters.Add(param);
                param = new SqlParameter("@answer", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = answer;
                com.Parameters.Add(param);
                param = new SqlParameter("@username", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = username;
                com.Parameters.Add(param);
                com.ExecuteNonQuery();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public void editUserAccount(Account a)
        {
            username = a.getUsername();
            password = a.getPass();
            usertype = a.getUsertype();
            status = a.getStatus();
            question = a.getQuestion();
            answer = a.getAnswer();
            int i = 0;

            try
            {
                connect();

                com = new SqlCommand("dbo.editAccount", getCon());
                com.CommandType = CommandType.StoredProcedure;

                param = new SqlParameter("@pass", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = password;
                com.Parameters.Add(param);
                param = new SqlParameter("@type", SqlDbType.Bit);
                param.Direction = ParameterDirection.Input;
                param.Value = usertype;
                com.Parameters.Add(param);
                param = new SqlParameter("@status", SqlDbType.Bit);
                param.Direction = ParameterDirection.Input;
                param.Value = status;
                com.Parameters.Add(param);
                param = new SqlParameter("@question", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = question;
                com.Parameters.Add(param);
                param = new SqlParameter("@answer", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = answer;
                com.Parameters.Add(param);
                param = new SqlParameter("@attempt", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = i;
                com.Parameters.Add(param);
                param = new SqlParameter("@user", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = username;
                com.Parameters.Add(param);

                com.ExecuteNonQuery();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public ArrayList getUserList(Boolean type, String username)
        {
            Boolean usertype = type;
            String u = username;
            ArrayList list = new ArrayList();

            try
            {
                connect();

                com = new SqlCommand("select username from Accounts where usertype = @type and username != @u", getCon());
                param = new SqlParameter("@type", SqlDbType.Bit);
                param.Direction = ParameterDirection.Input;
                param.Value = usertype;
                com.Parameters.Add(param);
                param = new SqlParameter("@u", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = u;
                com.Parameters.Add(param);
                read = com.ExecuteReader();

                while (read.Read())
                {
                    list.Add(Convert.ToString(read["username"]));
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return list;
        }

        public Account getUser(String user)
        {
            Account a = new Account();
            a.setUsername(user);

            try
            {
                connect();

                com = new SqlCommand("select * from Accounts where username = @username", getCon());
                param = new SqlParameter("@username", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = a.getUsername();
                com.Parameters.Add(param);
                read = com.ExecuteReader();

                while (read.Read())
                {
                    a.setPass(Convert.ToString(read["password"]));
                    a.setUsertype(Convert.ToBoolean(read["usertype"]));
                    a.setStatus(Convert.ToBoolean(read["status"]));
                    a.setQuestion(Convert.ToString(read["securityquestion"]));
                    a.setAnswer(Convert.ToString(read["securityanswer"]));
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return a;
        }

        public Boolean userExists(String user)
        {
            Boolean exist = false;
            String username = user;

            try
            {
                connect();

                com = new SqlCommand("select count(*) as verify from Accounts where username = @username", getCon());
                param = new SqlParameter("@username", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = username;
                com.Parameters.Add(param);
                read = com.ExecuteReader();

                while (read.Read())
                {
                    exist = Convert.ToBoolean(read["verify"]);
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return exist;
        }

        public int getAttempts(Account a)
        {
            int count = 0;

            try
            {
                connect();

                com = new SqlCommand("select attempts from Accounts where username = @username", getCon());
                param = new SqlParameter("@username", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = a.getUsername();
                com.Parameters.Add(param);
                read = com.ExecuteReader();

                if (read.Read())
                {
                    count = Convert.ToInt32(read.GetValue(0));
                }
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
            return count;
        }

        public void resetAttempts(string username)
        {
            try
            {
                connect();

                com = new SqlCommand("update Accounts set attempts = 0 where username = @username", getCon());
                param = new SqlParameter("@username", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = username;
                com.Parameters.Add(param);
                com.ExecuteNonQuery();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public void incAttempts(string username)
        {
            try
            {
                connect();

                com = new SqlCommand("update Accounts set attempts = attempts + 1 where username = @username", getCon());
                param = new SqlParameter("@username", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = username;
                com.Parameters.Add(param);
                com.ExecuteNonQuery();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }
    }
}